package com.ssplayer.database;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

public class DBHelper extends SQLiteOpenHelper {

	private final static String DB_NAME = "ssplayer.db";
	private final static String MUSICLIST = "music_list_table";// 歌单表
	private final static String MUSICLIST_ID = "list_id";
	private final static String MUSICLIST_NAME = "list_name";
	private final static String LISTRELATIVE = "list_music_table";// 关系表
	private final static String MUSIC_ID = "music_id";

	private final static String SQL_CREATE_MUSICLIST = "CREATE TABLE "
			+ MUSICLIST + "(" + MUSICLIST_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT," + MUSICLIST_NAME
			+ " TEXT UNIQUE)";

	private final static String SQL_CREATE_LISTRELATIVE = "CREATE TABLE "
			+ LISTRELATIVE + " (" + MUSICLIST_ID + " INTEGER," + MUSIC_ID
			+ " INTEGER)";

	public DBHelper(Context context) {
		super(context, DB_NAME, null, 1);
	}

	public DBHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(SQL_CREATE_MUSICLIST);
		db.execSQL(SQL_CREATE_LISTRELATIVE);
		db.execSQL("insert into " + MUSICLIST + " (" + MUSICLIST_NAME
				+ ") values ('我喜欢')");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

	/**
	 * 新建播放列表
	 * 
	 * @param listname
	 */
	public void createNewList(String listname) {
		SQLiteDatabase db = getWritableDatabase();
		db.execSQL("insert into " + MUSICLIST + " (" + MUSICLIST_NAME
				+ ") values ('" + listname + "')");
	}

	/**
	 * 删除播放列表
	 */
	public void deleteList(int listid) {
		SQLiteDatabase db = getWritableDatabase();
		db.execSQL("delete from " + MUSICLIST + " where " + MUSICLIST_ID + "="
				+ listid);
		db.execSQL("delete from " + LISTRELATIVE + " where " + MUSICLIST_ID
				+ "=" + listid);
	}

	/**
	 * 查询播放列表
	 * 
	 * @return
	 */
	public Cursor getLists() {
		SQLiteDatabase db = getWritableDatabase();
		return db.query(MUSICLIST, null, null, null, null, null, null);
	}

	/**
	 * 查询播放列表里的音乐
	 * 
	 * @return
	 */
	public List<String> getListMusics(int currentlist) {
		SQLiteDatabase db = getWritableDatabase();
		String[] columns = new String[] { MUSIC_ID };
		String selection = MUSICLIST_ID + "=?";
		String[] selectionArgs = new String[] { String.valueOf(currentlist) };
		Cursor cursor = db.query(LISTRELATIVE, columns, selection,
				selectionArgs, null, null, null);
		// 将列表中的音乐ID存入数组
		List<String> musicidlist = new ArrayList<>();
		while (cursor.moveToNext()) {
			musicidlist.add(String.valueOf(cursor.getInt(0)));
		}
		return musicidlist;
	}

	/**
	 * 添加音乐到播放列表
	 * 
	 * @param listname
	 */
	public boolean addMusicToList(int listid, int musicid) {
		SQLiteDatabase db = getWritableDatabase();
		Cursor cursor = db.rawQuery("select * from " + LISTRELATIVE + " where "
				+ MUSICLIST_ID + "=? and " + MUSIC_ID + "=?", new String[] {
				String.valueOf(listid), String.valueOf(musicid) });
		// 如果音乐在列表中存在，则不会插入
		if (cursor.moveToNext()) {
			return false;
		}
		SQLiteStatement statement = db.compileStatement("insert into "
				+ LISTRELATIVE + " (" + MUSICLIST_ID + "," + MUSIC_ID
				+ ") values (?,?)");
		statement.bindLong(1, listid);
		statement.bindLong(2, musicid);
		statement.execute();
		return true;
	}

	/**
	 * 添加音乐到播放列表
	 * 
	 * @param listname
	 */
	public boolean removeMusicFromList(int listid, int musicid) {
		SQLiteDatabase db = getWritableDatabase();
		SQLiteStatement statement = db.compileStatement("delete from "
				+ LISTRELATIVE + " where " + MUSICLIST_ID + "=? and "
				+ MUSIC_ID + "=?");
		statement.bindLong(1, listid);
		statement.bindLong(2, musicid);
		statement.execute();
		return true;
	}

	/**
	 * 列表重命名
	 * 
	 * @param id
	 * @param name
	 */
	public void renameList(long id, String name) {
		SQLiteDatabase db = getWritableDatabase();
		SQLiteStatement statement = db.compileStatement("update " + MUSICLIST
				+ " set " + MUSICLIST_NAME + "=? where " + MUSICLIST_ID + "=?");
		statement.bindString(1, name);
		statement.bindLong(2, id);
		statement.execute();
	}
}
